package com.sys.service;

import com.sys.GlobalVariable;
import com.sys.entity.Card;
import com.sys.entity.Customer;
import com.sys.exception.DuplicateCardException;
import com.sys.utils.DbUtil4;
import com.sys.utils.DbUtil4Factory;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class CustomerService {
    public List<Map<String,Object>> showList(int uid, String cname,String isvip) throws Exception {
        DbUtil4 db = null;
        List<Map<String,Object>> list = null;
        String sql = "SELECT cid,cname,csex," +
                " cage,cphone,t_customer.tid,t_trainer.tname," +
                " isvip," +
                " t_customer.create_time," +
                " t_customer.modify_time," +
                " t_customer.isdel " +
                " FROM t_customer,t_trainer " +
                " where t_customer.isdel = 0 and t_customer.uid = ? and  " +
                " t_customer.tid = t_trainer.tid ";
        List paramList = new ArrayList();
        paramList.add(uid);
        if(cname!=null && !"".equals(cname)){
            sql += " and cname like ?";
            paramList.add("%"+cname+"%");
        }
        if(isvip!=null && !"".equals(isvip) && !"-1".equals(isvip)){
            sql += " and isvip like ?";
            paramList.add(isvip+"%");
        }
        sql += " order by modify_time desc,cid ";
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            list = db.query(sql, paramList.toArray() );
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return list;
    }
    public List<Map<String,Object>> showTrainerList() throws Exception {
        DbUtil4 db = null;
        List<Map<String,Object>> list = null;
        String sql = "SELECT tid,tname from t_trainer";
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            list = db.query(sql,new Object[]{});
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return list;
    }
    public void add(String cname, String csex,String cage,String cphone,String tid,String isvip, Integer uid) throws Exception {
        DbUtil4 db = null;
        String selectSQL = "select count(1) as num from t_customer where cname = ? and csex=?  and cage=? and uid =? ";
        String sql = "INSERT INTO t_customer (cname,csex,cage,cphone,tid,isvip,uid) VALUES (?,?,?,?,?,?,?)";
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            List<Map<String, Object>> list = db.query(selectSQL, new Object[]{cname, csex, cage,uid});
            // 由于查询的是 select count(1) ，所以一定有一个值
//            Map<String, Object> map = list.get(0);
//            Long num = (Long) map.get("num");
            // 将上面两行代码合成一行
            Long num = (Long)  list.get(0).get("num");
            if(num == 0){ // 没有重复的记录
                db.update(sql, new Object[]{cname,csex,cage,cphone,tid,isvip,uid});
            }else{ // 如果名片存在，抛出异常
                throw new DuplicateCardException("顾客已经存在！");
            }

        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw e;
            }
        }
    }
    public int delete(int cid) throws Exception {
        DbUtil4 db = null;
        String sql = "UPDATE t_customer SET isdel = 1 WHERE cid = ?";
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            int j = db.update(sql, new Object[]{cid});
            return j;
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw e;
            }
        }
    }
    public Customer showById(String cid) throws Exception{
        List<Customer> list = null;
        DbUtil4 db = null;
        boolean t = false;
        String sql = "SELECT cid,cname,csex,cage,cphone,tid,isvip,create_time,modify_time,isdel,uid FROM t_customer WHERE cid = ? and isdel = 0";
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            list = db.query(sql, new Object[]{ cid }, Customer.class);
            if(list!=null && !list.isEmpty()){
                Customer customer = list.get(0);
                return customer;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw e;
            }
        }
        return null;
    }
    public int update(String cname, String csex, String cage,String cphone,String isvip,String tid, int cid) throws Exception {
        DbUtil4 db = null;
        String sql = "update t_customer set cname = ?, csex = ?, cage = ? ,cphone = ?,isvip = ?,tid = ? where cid = ?";
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            int i = db.update(sql, new Object[]{cname, csex,cage,cphone,isvip,tid,cid});
            return i;
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw e;
            }
        }
    }
}
